package com.it2297.model;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import com.it2297.main.App;
import com.it2297.main.CompanyApp;
import com.it2297.main.UserApp;


public class Job {
	private String title;
	private	String description;
	private String requirement;
	private String duty;
	private String industryType;
	private String employmentType;
	private String workingStartTime;
	private String workingEndTime;
	private double salary;
	private String benefits;
	private int companyId;
	private String companyName;
	private String search;
	//private String match;
	private String status;
	private String applicantNRIC;
	private String jobId;
	private String date;
	private String applicantName;
	private String email;
	private int phone;
	private String applicationStatus;
	private String companyEmail;
	
	public String getCompanyEmail() {
		return companyEmail;
	}
	public void setCompanyEmail(String companyEmail) {
		this.companyEmail = companyEmail;
	}
	public String getApplicationStatus() {
		return applicationStatus;
	}
	public void setApplicationStatus(String applicationStatus) {
		this.applicationStatus = applicationStatus;
	}

	private int index;
	private int count;
	
	public int getIndex() {
		return index;
	}
	public void setIndex(int index) {
		this.index = index;
	}
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}

	
	
	public String getPhoneNum() {
		return phoneNum;
	}
	public void setPhoneNum(String phoneNum) {
		this.phoneNum = phoneNum;
	}

	private String phoneNum;
	private String noResult;
	private String additionalInformation;
	
	//Job job = new Job();
	
	public String getAdditionalInformation() {
		return additionalInformation;
	}
	public void setAdditionalInformation(String additionalInformation) {
		this.additionalInformation = additionalInformation;
	}
	public String getNoResult() {
		return noResult;
	}
	public void setNoResult(String noResult) {
		this.noResult = noResult;
	}
	public int getPhone() {
		return phone;
	}
	public void setPhone(int phone) {
		this.phone = phone;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getApplicantName() {
		return applicantName;
	}
	public void setApplicantName(String applicantName) {
		this.applicantName = applicantName;
	}
	public String getDate() {
		return date;
	}
	public void setDate(String date) {
		this.date = date;
	}
	public String getJobId() {
		return jobId;
	}
	public void setJobId(String jobId) {
		this.jobId = jobId;
	}
	public String getApplicantNRIC() {
		return applicantNRIC;
	}
	public void setApplicantNRIC(String applicantNRIC) {
		this.applicantNRIC = applicantNRIC;
	}
	public String getStatus() {
		return status;
	}
	public void setStatus(String status) {
		this.status = status;
	}
	public String getCompanyName() {
		return companyName;
	}
	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}
	public int getCompanyId() {
		return companyId;
	}
	public void setCompanyId(int companyId) {
		this.companyId = companyId;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getRequirement() {
		return requirement;
	}
	public void setRequirement(String requirement) {
		this.requirement = requirement;
	}
	public String getDuty() {
		return duty;
	}
	public void setDuty(String duty) {
		this.duty = duty;
	}
	public String getIndustryType() {
		return industryType;
	}
	public void setIndustryType(String industryType) {
		this.industryType = industryType;
	}
	public String getEmploymentType() {
		return employmentType;
	}
	public void setEmploymentType(String employmentType) {
		this.employmentType = employmentType;
	}
	public String getWorkingStartTime() {
		return workingStartTime;
	}
	public void setWorkingStartTime(String workingStartTime) {
		this.workingStartTime = workingStartTime;
	}
	public String getWorkingEndTime() {
		return workingEndTime;
	}
	public void setWorkingEndTime(String workingEndTime) {
		this.workingEndTime = workingEndTime;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public String getBenefits() {
		return benefits;
	}
	public void setBenefits(String benefits) {
		this.benefits = benefits;
	}
	public String getSearch() {
		return search;
	}
	public void setSearch(String search) {
		this.search = search;
	}

	public static Job retrieveDetails(String title) {
		Job job = new Job();
		ResultSet rs = null;

		try {
			App.mySqlConnection.open();

			
			String sql="SELECT * FROM americano.JOB_VIEW WHERE title = '" + title + "'";
			rs = App.mySqlConnection.executeQuery(sql);

			if (rs.next()) {

				job.setTitle(rs.getString("title"));
				job.setDescription(rs.getString("description"));
				job.setRequirement(rs.getString("requirement"));
				job.setDuty(rs.getString("duty"));
				job.setIndustryType(rs.getString("industry_type"));
				job.setEmploymentType(rs.getString("employment_type"));
				job.setWorkingStartTime(rs.getString("working_start_time"));
				job.setWorkingEndTime(rs.getString("working_end_time"));
				job.setSalary(rs.getDouble("salary"));
				job.setBenefits(rs.getString("benefit"));
				job.setCompanyName(rs.getString("name"));
				job.setJobId(rs.getString("job_id"));
				
			
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		finally {
			App.mySqlConnection.close();
		}
		return job;
	}

	public ArrayList<String> retrieveSearch(Job job){
		ArrayList<String> arr_search_match = new ArrayList<String>();
		ResultSet rs = null;
		String title = job.getSearch().toString();
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.JOB_VIEW WHERE title LIKE '"  + '%' + title + '%' + "' and status = 'Active'";
			rs=App.mySqlConnection.executeQuery(sql);
			
			
			if(!rs.next()){
				job.setTitle("");
			}
			else{
				rs.beforeFirst();
				
					while(rs.next()){	
						arr_search_match.add(rs.getString("title"));	
					}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return arr_search_match;
	}
	
	public static boolean createJob(Job job){
		boolean success = false;
		
		String title= job.getTitle().toString();
		String desc = job.getDescription().toString();
		String req=job.getRequirement().toString();
		String duty=job.getDuty().toString();
		String it=job.getIndustryType().toString();
		String et=job.getEmploymentType().toString();
		String wst=job.getWorkingStartTime().toString();
		String wet=job.getWorkingEndTime().toString();
		double salary=job.getSalary();
		String benefit=job.getBenefits().toString();
		int cID=job.getCompanyId();
		String status="Active";
		
		try{
			App.mySqlConnection.open();
			
			String sql = "INSERT INTO americano.job(title,description,requirement,duty,industry_type,employment_type,working_start_time,working_end_time,salary,benefit,status,company_id)";
			sql+="VALUES ('" + title + "','" + desc + "','" + req + "','" + duty + "','" + it + "','" + et + "','" + wst +"','"+ wet + "','"+ salary + "','" + benefit + "','" + status + "','"+ cID +"')";
			
			App.mySqlConnection.executeUpdate(sql);
				success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
			
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	

	public static boolean createJob2(Job job){
		boolean success = false;
		
		String title= job.getTitle().toString();
		String desc = job.getDescription().toString();
		String req=job.getRequirement().toString();
		String duty=job.getDuty().toString();
		String it=job.getIndustryType().toString();
		String et=job.getEmploymentType().toString();
		String wst=job.getWorkingStartTime().toString();
		String wet=job.getWorkingEndTime().toString();
		double salary=job.getSalary();
		String benefit=job.getBenefits().toString();
		int cID=job.getCompanyId();
		String status="Active";
		
		try{
			App.mySqlConnection.open();
			
			String sql = "UPDATE americano.job SET title = '" + title + "', description = '" + desc + "' , requirement = '" + req + "', duty = '" + duty + "', industry_type = '" + it + "', employment_type= '" + et + "', working_start_time='" + wst + "', working_end_time = '" + wet + "', salary ='" + salary + "',benefit ='" + benefit + "', status='" + status + "' , company_id ='" + cID + "'";
			sql += "WHERE title='" + title + "'";
		
			App.mySqlConnection.executeUpdate(sql);
				success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
			
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	

	public static boolean IsTitleExist(String title){
		boolean result = false;
		ResultSet rs = null;
		String sql = "SELECT * FROM americano.job WHERE title='" + title + "' and status = 'Active'";
		
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next())
				result = true;
			else
				result = false;
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return result;
	}
	
	public static boolean IsTitleExist2(String title){
		boolean result = false;
		ResultSet rs = null;
		String sql = "SELECT * FROM americano.job WHERE title='" + title + "' and status = 'Inactive'";
		
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next())
				result = true;
			else
				result = false;
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return result;
	}
	
	/*
	public ArrayList<String> retrieveAllJobsByCompany(){
		ArrayList<String> arr_jobs = new ArrayList<String>();
		ResultSet rs = null;
		
		Job job=new Job();
	//	String cname=CompanyApp.getCurrentUser().getCompanyName();
		String cname="MDR Limited";
		try {
			App.mySqlConnection.open();
			String sql = "SELECT title FROM americano.JOB_VIEW WHERE status = 'Active' AND name = '" + cname + "'";
			rs=App.mySqlConnection.executeQuery(sql);
				while(rs.next()){	
					arr_jobs.add(rs.getString("title"));	
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally{
				App.mySqlConnection.close();
			}
			return arr_jobs;
	}
	*/
	
	
	public static boolean updateStatusToInactive(Job job) {
		boolean success = false;

		String status="Inactive";
		String title=job.getTitle().toString();
		String jobid=job.getJobId().toString();
		String status1="Inactive";
		
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.job SET status = '" + status + "'";
			sql += "WHERE title='" + title + "'";
			
			App.mySqlConnection.executeUpdate(sql);
			success = true;
			String sql2="UPDATE americano.application SET status = '" + status1 + "'";
		  	sql2 += "WHERE fk_job_id = '" + jobid + "'";
		  	//System.out.println(sql2);
			App.mySqlConnection.executeUpdate(sql2);
			success=true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	public static boolean applyForJob(Job job){
		boolean success = false;
		ResultSet rs = null;
		
		Timestamp date=new Timestamp(System.currentTimeMillis());
		String application_status="Pending";
		String NRIC=job.getApplicantNRIC().toString();
		String jobTitle=job.getTitle().toString();
		String additionalInfo=job.getAdditionalInformation().toString();
		int resumeID=1;
		String status = "Active";
		try{
			App.mySqlConnection.open();
			
			String jobId="Select job_id from americano.job WHERE title='" + jobTitle + "'";
			rs=App.mySqlConnection.executeQuery(jobId);
			while(rs.next()){	
				job.setJobId(rs.getString("job_id"));	
			}
			String jobID=job.getJobId();
			
			String sql = "INSERT INTO americano.application(date_apply,application_status,fk_nric,fk_job_id,fk_resume_id,status,additional_info)";
			sql+="VALUES ('" + date + "','" + application_status + "','" + NRIC + "','" + jobID + "','" + resumeID  + "','" + status + "', '" + additionalInfo + "')";
			
			App.mySqlConnection.executeUpdate(sql);
				success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
			
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	public static boolean applyForJob2(Job job){
		boolean success = false;
		ResultSet rs = null;
		
		Timestamp date=new Timestamp(System.currentTimeMillis());
		String application_status="Pending";
		String NRIC=job.getApplicantNRIC().toString();
		String jobTitle=job.getTitle().toString();
		String additionalInfo=job.getAdditionalInformation().toString();
		int resumeID=1;
		String status = "Active";
		try{
			App.mySqlConnection.open();
			
			String jobId="Select job_id from americano.job WHERE title='" + jobTitle + "'";
			rs=App.mySqlConnection.executeQuery(jobId);
			while(rs.next()){	
				job.setJobId(rs.getString("job_id"));	
			}
			String jobID=job.getJobId();
			
			App.mySqlConnection.open();
			String sql = "UPDATE americano.application SET date_apply = '" + date + "', application_status = '" + application_status + "' , fk_nric = '" + NRIC + "', fk_job_id = '" + jobID + "', fk_resume_id = '" + resumeID + "', status = '" + status + "' , additional_info = '" + additionalInfo + "'";
			sql += "WHERE fk_nric='" + NRIC + "' and fk_job_id = '" + jobID + "'";
			
			App.mySqlConnection.executeUpdate(sql);
				success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
			
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	
	public static Job getJobID(Job job){
		ResultSet rs = null;
		String jobTitle=job.getTitle().toString();
		String sql = "Select job_id from americano.job WHERE title='" + jobTitle + "'";
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			while (rs.next()){
				job.setJobId(rs.getString("job_id"));
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return job;
	}
	
	public static boolean checkForDuplicateApplication(Job job){
		boolean result = false;
		ResultSet rs = null;
		
		String NRIC=job.getApplicantNRIC().toString();
		String jobId=job.getJobId();
		String status="Active";
		String sql = "SELECT * FROM americano.application WHERE fk_nric='" + NRIC + "' AND fk_job_id = '" + jobId + "' AND status='" + status +"'";
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next())
				result = true;
			else
				result = false;	
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return result;
	}
	
	public static boolean checkForDuplicateApplication2(Job job){
		boolean result = false;
		ResultSet rs = null;
		
		String NRIC=job.getApplicantNRIC().toString();
		String jobId=job.getJobId();
		String status="Inactive";
		String sql = "SELECT * FROM americano.application WHERE fk_nric='" + NRIC + "' AND fk_job_id = '" + jobId + "' AND status='" + status +"'";
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next())
				result = true;
			else
				result = false;	
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return result;
	}
	
	public ArrayList<String> retrieveApplicants(Job job){
		ArrayList<String> arr_Applicants = new ArrayList<String>();
		ResultSet rs = null;
		String jobId= job.getJobId().toString();
		String status = "Active";
		String application_status = "Pending";
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.application where fk_job_id='" + jobId + "' and status = '" + status + "' and application_status = '" + application_status + "'";
			rs=App.mySqlConnection.executeQuery(sql);
			if(!rs.next()){
				job.setNoResult("");
			}
			else{
				rs.beforeFirst();
					while(rs.next()){
						arr_Applicants.add(rs.getString("fk_nric"));
					}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return arr_Applicants;
	}

	public static Job retrieveApplicantDetails(Job job) {
		ResultSet rs = null;
		String nric = job.getApplicantNRIC().toString();
		String jobTitle = job.getTitle().toString();
		String status="Active";
		String application_status = "Pending";
		try {
			App.mySqlConnection.open();

			
			String sql="SELECT * FROM americano.APPLICANT_VIEW WHERE applicant_nric = '" + nric + "' and job_title = '" + jobTitle + "' and status= '" +status +"' and application_status = '" + application_status + "'";
			rs = App.mySqlConnection.executeQuery(sql);	
		//	System.out.println(sql);
			
			if (rs.next()) {
				
				job.setDate(rs.getString("date_of_application"));
				job.setTitle(rs.getString("job_title"));
				job.setApplicantName(rs.getString("applicant_name"));
				job.setApplicantNRIC(rs.getString("applicant_nric"));
				//job.setPhone(rs.getInt("applicant_phone"));
				job.setPhoneNum(rs.getString("applicant_phone"));
				job.setCompanyName(rs.getString("company_name"));	
				job.setEmail(rs.getString("applicant_email"));
				job.setJobId(rs.getString("job_id"));
				job.setAdditionalInformation(rs.getString("additional_info"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		finally {
			App.mySqlConnection.close();
		}
		return job;
	}
	public ArrayList<String> getAllJobApplicationsByNeedy(Job job){
		ArrayList<String> arr_AllJobs = new ArrayList<String>();
		ResultSet rs = null;
		String nric= job.getApplicantNRIC().toString();
		String status = "Active";
		String job_status="Active";
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.APPLICANT_VIEW where applicant_nric='" + nric + "' and status = '" + status + "' and job_status = '" + job_status + "' and application_status = 'Pending'";
			rs=App.mySqlConnection.executeQuery(sql);
			if(!rs.next()){
				job.setNoResult("");
			}
			else{
				rs.beforeFirst();
					while(rs.next()){
						arr_AllJobs.add(rs.getString("job_title"));
					}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return arr_AllJobs;
	}
	
	
	public static boolean updateApplicationStatusToInactive(Job job) {
		boolean success = false;

		String status="Inactive";
		String id=job.getJobId().toString();
		String NRIC=job.getApplicantNRIC();
		
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.application SET status = '" + status + "'";
			sql += "WHERE fk_job_id='" + id + "' and fk_nric = '" +  NRIC +"' and application_status = 'Pending'";
			
			App.mySqlConnection.executeUpdate(sql);
			success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	public static boolean shortlistApplicant(Job job) {
		boolean success = false;

		String status="Active";
		String id=job.getJobId().toString();
		String NRIC=job.getApplicantNRIC();
		String application_status = "Shortlisted";
		try {
			App.mySqlConnection.open();
			
			String sql = "UPDATE americano.application SET application_status = '" + application_status + "'";
			sql += "WHERE fk_job_id = '" + id + "' and fk_nric = '" + NRIC + "' and status ='" + status + "'";

			App.mySqlConnection.executeUpdate(sql);
			success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	
	
	
	
	
	
	
	
	
	/*****ManageApplication*****/
	public static ArrayList<Job> getMultipleItem(int index, int count) {
		ArrayList<Job> multi = new ArrayList<Job>();
		String nric= UserApp.getCurrentUser().getNric();
		Job job = new Job();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.APPLICANT_VIEW where applicant_nric= ? and status = 'Active' and job_status = 'Active' and application_status = 'Pending' LIMIT ?,?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setString(1, nric);
			ps.setInt(2, index);
			ps.setInt(3, count);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Job job2 = new Job();
				job2.setTitle(rs.getString("job_title"));
				job2.setJobId(rs.getString("job_id"));
				job2.setApplicantName(rs.getString("applicant_name"));
				job2.setApplicantNRIC(rs.getString("applicant_nric"));
				job2.setCompanyName(rs.getString("company_name"));
				job2.setApplicationStatus(rs.getString("application_status"));
				job2.setDate(rs.getString("date_of_application"));
				job2.setEmail(rs.getString("applicant_email"));
				//job2.setPhone(rs.getString("applicant_phone"));
				job2.setAdditionalInformation(rs.getString("additional_info"));
				multi.add(job2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return multi;
	}
	
	public static int getTotalNumOfApplicationsByNeedy() {
		int recordNum = 0;
		Job job = new Job();
		String nric = UserApp.getCurrentUser().getNric();
		//String nric = "S9834792H";
		try {
			App.mySqlConnection.open();
			String sql = "SELECT COUNT(*) FROM americano.APPLICANT_VIEW WHERE status='Active' and applicant_nric = ? and  application_status = 'Pending' and job_status= 'Active'";
			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(sql);
			preparedStatement.setString(1, nric);
			ResultSet resultSet = preparedStatement.executeQuery();
			
			
			
			if (resultSet.next()) {
				recordNum = resultSet.getInt(1);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return recordNum;
	}
	
	/****MANAGEJOBAPPLICANTS****/
	public ArrayList<String> retrieveAllJobsByCompany(){
		ArrayList<String> arr_jobs = new ArrayList<String>();
		ResultSet rs = null;
		
		Job job=new Job();
		String cname=CompanyApp.getCurrentUser().getCompanyName();
	//	String cname="MDR Limited";
		try {
			App.mySqlConnection.open();
			String sql = "SELECT title FROM americano.JOB_VIEW WHERE status = 'Active' AND name = '" + cname + "'";
			rs=App.mySqlConnection.executeQuery(sql);
				while(rs.next()){	
					arr_jobs.add(rs.getString("title"));	
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally{
				App.mySqlConnection.close();
			}
			return arr_jobs;
	}
	
	public static ArrayList<Job> getMultipleApplicants(int index, int count,String title) {
		ArrayList<Job> multi = new ArrayList<Job>();
		//Job job = new Job();
		//	String job_title = "Technician";
	//	String job_title = job.getTitle().toString();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.APPLICANT_VIEW where job_title= ? and status = 'Active' and application_status = 'Pending' LIMIT ?,?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setString(1, title);
			ps.setInt(2, index);
			ps.setInt(3, count);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Job job2 = new Job();
				job2.setTitle(rs.getString("job_title"));
				job2.setJobId(rs.getString("job_id"));
				job2.setApplicantName(rs.getString("applicant_name"));
				job2.setApplicantNRIC(rs.getString("applicant_nric"));
				job2.setCompanyName(rs.getString("company_name"));
				job2.setApplicationStatus(rs.getString("application_status"));
				job2.setDate(rs.getString("date_of_application"));
				job2.setEmail(rs.getString("applicant_email"));
				int phoneNum = rs.getInt("applicant_phone");
				job2.setPhone(phoneNum);
				job2.setAdditionalInformation(rs.getString("additional_info"));
				multi.add(job2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return multi;
	}
	
	public static int getTotalNumOfApplicantsOfJob(String title) {
		int recordNum = 0;
		//Job job = new Job();
	//	String job_title = "Technician";
	//	String job_title = job.getTitle().toString();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT COUNT(*) FROM americano.APPLICANT_VIEW WHERE status='Active' and job_title = ? and  application_status = 'Pending' and job_status= 'Active'";
			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(sql);
			preparedStatement.setString(1, title);
			ResultSet resultSet = preparedStatement.executeQuery();
			
			
			
			if (resultSet.next()) {
				recordNum = resultSet.getInt(1);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return recordNum;
	}
	
	/******VIEW SHORTLISTED APPLICATIONS)******/
	public static ArrayList<Job> getMultipleShortlisted(int index, int count) {
		ArrayList<Job> multi = new ArrayList<Job>();
		//String nric= UserApp.getCurrentUser().getNric();
		String nric = "S9834792H";
		Job job = new Job();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.APPLICANT_VIEW where applicant_nric= ? and status = 'Active' and job_status = 'Active' and application_status = 'Shortlisted' LIMIT ?,?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setString(1, nric);
			ps.setInt(2, index);
			ps.setInt(3, count);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Job job2 = new Job();
				job2.setTitle(rs.getString("job_title"));
				job2.setJobId(rs.getString("job_id"));
				job2.setApplicantName(rs.getString("applicant_name"));
				job2.setApplicantNRIC(rs.getString("applicant_nric"));
				job2.setCompanyName(rs.getString("company_name"));
				job2.setApplicationStatus(rs.getString("application_status"));
				job2.setDate(rs.getString("date_of_application"));
				job2.setEmail(rs.getString("applicant_email"));
				//job2.setApplicationPhone(rs.getString("applicant_phone"));
				job2.setAdditionalInformation(rs.getString("additional_info"));
				multi.add(job2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return multi;
	}
	
	public static int getTotalNumOfShortlistedByNeedy() {
		int recordNum = 0;
		Job job = new Job();
	//	String nric = UserApp.getCurrentUser().getNric();
		String nric = "S9834792H";
		try {
			App.mySqlConnection.open();
			String sql = "SELECT COUNT(*) FROM americano.APPLICANT_VIEW WHERE status='Active' and applicant_nric = ? and  application_status = 'Shortlisted' and job_status= 'Active'";
			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(sql);
			preparedStatement.setString(1, nric);
			ResultSet resultSet = preparedStatement.executeQuery();
			
			
			
			if (resultSet.next()) {
				recordNum = resultSet.getInt(1);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return recordNum;
	}
	
}
